﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DelonixRegiaHotel.Classes;

namespace DelonixRegiaHotel.DBManager
{
    class ManageCustomerDBManager
    {
        //customer retrieve
        public static ArrayList GetAllCustomerDetails()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from Customer";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Customer temp = new Customer();
                    temp.CustomerID = Convert.ToString(dr["customerID"]);
                    temp.FirstName = Convert.ToString(dr["firstName"]);
                    temp.LastName = Convert.ToString(dr["lastName"]);
                    temp.HomeAddress = Convert.ToString(dr["homeAddress"]);
                    temp.PostalCode = Convert.ToInt32(dr["postalCode"]);
                    temp.Country = Convert.ToString(dr["country"]);
                    temp.Dob = Convert.ToDateTime(dr["dob"]); ;
                    temp.ContactNo = Convert.ToInt32(dr["contactNo"]); ;
                    temp.Email = Convert.ToString(dr["email"]); ;
                    temp.Nric = Convert.ToString(dr["nric"]); ;
                    result.Add(temp);
                }

                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool TestWhetherCustomerIDExists(string customerID)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Customer where customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                if(dr.Read())
                {
                    sucess = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sucess;
        }

        public static bool TestWhetherCustomerIDExistsInCheckIn(string customerID)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from CheckInOut where customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    sucess = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sucess;
        }

        //customer create
        public static int CreateNewCustomer(Customer newCustomerInfo)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Insert into Customer values (@customerID, @firstName, @lastName, @homeAddress, @postalCode, @country, @dob, @contactNo, @email, @nric)";
                comm.Parameters.AddWithValue("@customerID", newCustomerInfo.CustomerID);
                comm.Parameters.AddWithValue("@firstName", newCustomerInfo.FirstName);
                comm.Parameters.AddWithValue("@lastName", newCustomerInfo.LastName);
                comm.Parameters.AddWithValue("@homeAddress", newCustomerInfo.HomeAddress);
                comm.Parameters.AddWithValue("@postalCode", newCustomerInfo.PostalCode);
                comm.Parameters.AddWithValue("@country", newCustomerInfo.Country);
                comm.Parameters.AddWithValue("@dob", newCustomerInfo.Dob);
                comm.Parameters.AddWithValue("@contactNo", newCustomerInfo.ContactNo);
                comm.Parameters.AddWithValue("@email", newCustomerInfo.Email);
                comm.Parameters.AddWithValue("@nric", newCustomerInfo.Nric);
                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowsInserted;
        }

        //customer update with insert to customer mod record
        public static int UpdateCustomer(Customer updateCustomerInfo)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Customer SET firstName=@firstName,lastName=@lastName,homeAddress=@homeAddress,postalCode=@postalCode,country=@country,dob=@dob,contactNo=@contactNo,email=@email,nric=@nric where staffID=@staffID";
                comm.Parameters.AddWithValue("@customerID", updateCustomerInfo.CustomerID);
                comm.Parameters.AddWithValue("@firstName", updateCustomerInfo.FirstName);
                comm.Parameters.AddWithValue("@lastName", updateCustomerInfo.LastName);
                comm.Parameters.AddWithValue("@homeAddress", updateCustomerInfo.HomeAddress);
                comm.Parameters.AddWithValue("@postalCode", updateCustomerInfo.PostalCode);
                comm.Parameters.AddWithValue("@country", updateCustomerInfo.Country);
                comm.Parameters.AddWithValue("@dob", updateCustomerInfo.Dob);
                comm.Parameters.AddWithValue("@contactNo", updateCustomerInfo.ContactNo);
                comm.Parameters.AddWithValue("@email", updateCustomerInfo.Email);
                comm.Parameters.AddWithValue("@nric", updateCustomerInfo.Nric);
                rowsupdated = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            InsertIntoCustomerModRecord(updateCustomerInfo.CustomerID);
            return rowsupdated;
        }

        public static void InsertIntoCustomerModRecord(string customerID)
        {
            try
            {
                SqlConnection conn = null;
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from CustomerModRecord where customerID=@customerID and staffID=@staffID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                comm.Parameters.AddWithValue("@staffID", StaticSession.StaffID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    conn.Close();
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn1;
                    comm1.CommandText = "update CustomerModRecord SET timeOfMod=@timeOfMod where customerID=@customerID and staffID=@staffID";
                    comm1.Parameters.AddWithValue("@customerID", customerID);
                    comm1.Parameters.AddWithValue("@timeOfMod", DateTime.Now);
                    comm1.Parameters.AddWithValue("@staffID", StaticSession.StaffID);
                    comm1.ExecuteNonQuery();
                    conn1.Close();
                }
                else
                {
                    conn.Close();
                    SqlConnection conn2 = null;
                    conn2 = new SqlConnection();
                    conn2.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn2.Open();
                    SqlCommand comm2 = new SqlCommand();
                    comm2.Connection = conn2;
                    comm2.CommandText = "insert into CustomerModRecord values (@customerID, @staffID, @timeOfMod)";
                    comm2.Parameters.AddWithValue("@customerID", customerID);
                    comm2.Parameters.AddWithValue("@staffID", StaticSession.StaffID);
                    comm2.Parameters.AddWithValue("@timeOfMod", DateTime.Now);
                    comm2.ExecuteNonQuery();
                    conn2.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static int DeleteCustomerInformationByCustomerID(string customerID) // group 2
        {
            DeleteCustomerCreditCardInfoByCustomerIDIfAny(customerID);
            DeleteCustomerModRecordByCustomerIDIfAny(customerID);
            int rowsDeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from Customer where customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowsDeleted;
        }

        public static void DeleteCustomerCreditCardInfoByCustomerIDIfAny(string customerID) //group 2
        {
            try
            {
                SqlConnection conn = null;
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from CreditCardInfo where customerID = @customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    conn.Close();
                    SqlConnection conn2 = null;
                    conn2 = new SqlConnection();
                    conn2.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn2.Open();
                    SqlCommand comm2 = new SqlCommand();
                    comm2.Connection = conn2;
                    comm2.CommandText = "delete from CreditCardInfo where customerID = @customerID";
                    comm2.Parameters.AddWithValue("@customerID",customerID);
                    comm2.ExecuteNonQuery();
                    conn2.Close();
                }
                conn.Close();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        public static void DeleteCustomerModRecordByCustomerIDIfAny(string customerID)//group 2
        {
            try
            {
                SqlConnection conn = null;
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from CustomerModRecord where customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    SqlConnection conn1 = null;
                    conn1 = new SqlConnection();
                    conn1.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                    conn1.Open();
                    SqlCommand comm1 = new SqlCommand();
                    comm1.Connection = conn1;
                    comm1.CommandText = "delete from CustomerModRecord where customerID=@customerID";
                    comm1.Parameters.AddWithValue("@customerID", customerID);
                    comm1.ExecuteNonQuery();
                    conn1.Close();
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //create credit card
        public static int CreateCreditCardInfo(CreditCardInfo newCreditCardInfo)
        {
            int rowsInserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "insert into CreditCardInfo values (@creditCardNo, @customerID, @creditCardHolderName, @creditCardExpDate)";
                comm.Parameters.AddWithValue("@creditCardNo", newCreditCardInfo.CreditCardNo);
                comm.Parameters.AddWithValue("@customerID", newCreditCardInfo.CustomerID);
                comm.Parameters.AddWithValue("@creditCardHolderName", newCreditCardInfo.CreditCardHolder);
                comm.Parameters.AddWithValue("@creditCardExpDate", newCreditCardInfo.CreditCardExpDate);
                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowsInserted;
        }
        
        //update credit card
        public static int UpdateCreditCardInfoByCardNum(CreditCardInfo updateCardInfo, Int64 oldCardNum)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CreditCardInfo SET creditCardNo=@creditCardNo,creditCardExpDate=@creditCardExpDate where creditCardNo=@oldCardNum";
                comm.Parameters.AddWithValue("@creditCardNo", updateCardInfo.CreditCardNo);
                comm.Parameters.AddWithValue("@creditCardExpDate", updateCardInfo.CreditCardExpDate);
                comm.Parameters.AddWithValue("@oldCardNum", oldCardNum);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        //delete credit card
        public static int DeleteCreditCardInfoByCardNum(Int64 cardNum)
        {
            int rowsDeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "delete from CreditCardInfo where creditCardNo=@creditCardNo";
                comm.Parameters.AddWithValue("@creditCardNo", cardNum);
                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return rowsDeleted;
        }

        //retrieve credit card
        public static ArrayList GetCreditInfoByCustomerId(string customerID)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CreditCardInfo where customerID=@customerID";
                comm.Parameters.AddWithValue("@customerID", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    CreditCardInfo temp = new CreditCardInfo();
                    temp.CustomerID = Convert.ToString(dr["customerID"]);
                    temp.CreditCardNo = Convert.ToInt64(dr["creditCardNo"]);
                    temp.CreditCardHolder = Convert.ToString(dr["creditCardHolderName"]);
                    temp.CreditCardExpDate = Convert.ToDateTime(dr["creditCardExpDate"]);
                    result.Add(temp);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool TestWhetherCardNumberExists(Int64 cardNo)
        {
            bool sucess = false;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from CreditCardInfo where creditCardNo=@creditCardNo";
                comm.Parameters.AddWithValue("@creditCardNo", cardNo);
                SqlDataReader dr = comm.ExecuteReader();
                if(dr.Read())
                {
                    sucess = true;
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return sucess;
        }
    }
}
